package com.saic.parse.impl;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;


public class ImportSubBrandParseAutoHome implements IImport{
	
	@Override
	public void importExcel(Connection conn) throws Exception {
		int flag = 0;
		int homeidentity = 0;
		int xcaridentity = 0;
		int bitautoidentity = 0;
		
		Statement stmt = conn.createStatement();

		InputStream stream = new FileInputStream("d:/subbrand.xls");
		
		//新的id  根据指定表的下一个增加量
		ResultSet resultSetNew = stmt.executeQuery("SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='"+DB_NAME+"' AND TABLE_NAME='t_autohome_subbrand'");
		resultSetNew.next();
		int newId = resultSetNew.getInt(1);				//新的id
		
		if(flag ==0){
			flag = newId;
		}
		System.out.println(flag);
		HSSFWorkbook wb = new HSSFWorkbook(stream);
		Sheet sheet = wb.getSheetAt(0);  
		//获取数据
		for(Row row:sheet){
			String autohomeBrandWebId = null;	// 子品牌的bitautowebid
			String autohomeid = null;			// 汽车之家主品牌id
			String name = null;					// 主品牌名
			String subbrandWebId = null;		// 汽车之家子品牌 webid
			String subbrandname = null;			// 子品牌名
			String desc = null;					// 描述
			String xcarid = null;				// xcar品牌id
			String subxcarid = null;			// xcar子品牌id
			String bitautoid = null;			// bitauto子品牌webid
			String url = null;
			for(Cell cell:row){
				//主品牌web_id
				if(cell.getColumnIndex()==0){
					autohomeid = cell.toString();
				}
				//主品牌name
				if(cell.getColumnIndex()==1){
					name = cell.toString();
				}
				//子品牌webid
				if(cell.getColumnIndex()==2){
					subbrandWebId = cell.toString();
				}
				//子品牌name
				if(cell.getColumnIndex()==3){
					subbrandname = cell.toString();
				}
				//bitautoid
				if(cell.getColumnIndex()==5){
					bitautoid = cell.toString();
				}
				//url
				if(cell.getColumnIndex()==7){
					xcarid = cell.toString();
				}
			}
			conn.setAutoCommit(false);
			

			
			ResultSet resultSet = stmt.executeQuery("select web_id from t_autohome_brand where name='" + name + "'");
			System.out.println(name);
			while(resultSet.next()){
				//获取autohome子品牌id
				autohomeBrandWebId = resultSet.getString(1);
			}
			
			//取出oldsubid  老的subid
//			PreparedStatement pstateUpdateSeries = 
//				conn.prepareStatement
//				("select subbrand.id from t_autohome_subbrand as subbrand,t_autohome_brand as brand where subbrand.brand_id = brand.id and brand.id=? and subbrand.name=?");
//			pstateUpdateSeries.setInt(1, homeidentity);
//			pstateUpdateSeries.setString(2, subbrandname);
//			ResultSet resultUpdateSeries = pstateUpdateSeries.executeQuery();
//			Integer oldId = null;
//			while(resultUpdateSeries.next()){
//				oldId = resultUpdateSeries.getInt(1);
//			}
//			System.out.println(oldId);
			
			PreparedStatement pstate = conn.prepareStatement("insert into t_autohome_subbrand (name,web_id,`desc`,brand_web_id) values(?,?,?,?)");
			pstate.setString(1, subbrandname);
			pstate.setString(2, subbrandWebId);
			pstate.setString(3, subbrandname);
			pstate.setString(4, autohomeBrandWebId);
			pstate.execute();
				
		}
		//删除新老的数据
		stmt.execute("delete from t_autohome_subbrand where id<"+flag);	
			
	}
}
